﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using WYJCore;
using WYJCore.Extension;
using WYJData.Orm;
using WYJWeb;

namespace WYJData.DatabaseAccess
{
    public class Database : IDatabase
    {

        private static readonly string dbname = "BLUE";
        //static public List<int> TransientErrorNumbers = new List<int> { 4060, 40197, 40501, 40613, 49918, 49919, 49920, 11001 };
        private static readonly string connectionString = "Data Source=47.92.217.144;database=master;uid=sa;pwd=~Zhiqing12345678";

        static Database()
        {
            using SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            new SqlCommand($"IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = '{dbname}') CREATE DATABASE {dbname}", connection)
            .ExecuteNonQuery();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="action"></param>
        public void AccessDb(Action<SqlCommand> action)
        {
            using SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            connection.ChangeDatabase(dbname);
            var sqlCommand = new SqlCommand(null, connection);
            action(sqlCommand);
        }

        public object GetModel(Table table, string sqlwhere)
        {
            object model = null;
            var sql = table.CreateQuerySql() + sqlwhere;
            AccessDb(sqlCommand =>
            {
                sqlCommand.Query(sql, dataReader =>
                {
                    model = table.ToModel(dataReader);
                });
            });
            return model;
        }


        public TModel GetModel<TModel>(Expression<Func<TModel, bool>> where) where TModel : class, IModel
        {
            var table = new Table(typeof(TModel));
            var sql = $" WHERE {table.TableName}.{where.Parse()}";
            return GetModel(table, sql) as TModel;
        }

        public TModel GetModelById<TModel>(int id) where TModel : class, IModel
        {
            var table = new Table(typeof(TModel));
            var sql = $" WHERE {table.TableName}.Id = {id}"; ;
            return GetModel(table, sql) as TModel;
        }
        public List<TModel> GetList<TModel>() where TModel : class, IModel
        {
            var table = new Table(typeof(TModel));
            var list = new List<TModel>();
            var sql = table.CreateQuerySql().ToString();
            AccessDb(sqlCommand =>
            {
                sqlCommand.Query(sql, dataReader =>
                {
                    list.Add(table.ToModel(dataReader) as TModel);
                });
            });
            return list;
        }

        public PageInfo GetPageList<TModel>(int pageIndex, int pageSize, string where) where TModel : class
        {
            var table = new Table(typeof(TModel));
            var sql = $"SELECT COUNT(*) FROM {table.TableName} WHERE {where};"
                + table.CreateQuerySql()
                .AppendFormat(" WHERE {0}.{1} ORDER BY {0}.Id ASC", table.TableName, where)
                .AppendFormat(" OFFSET ({0} - 1) * {1} rows FETCH next {1} rows only;", pageIndex, pageSize);
            var pageInfo = new PageInfo();
            AccessDb(sqlCommand =>
            {
                sqlCommand.Query(sql, (dataReader, result) =>
                {
                    if (result == 1) pageInfo.Count = (int)dataReader[0];
                    else pageInfo.List.Add(table.ToModel(dataReader));
                });
            });
            return pageInfo;
        }

        public void Update<TModel>(TModel Model) where TModel : class, IModel, new()
        {
            var table = new Table(Model);
            var sql = table.GetUpdateSql(Model);
            AccessDb(sqlCommand =>
            {
                sqlCommand.Execute(sql);
            });
        }
        public void Update<TModel>(object Model, Expression<Func<TModel, bool>> expression)
        {

            var TableName = "T_" + typeof(TModel).Name;
            var set = Model.GetType().GetProperties().Select(prop =>
            {
                var val = prop.GetValue(Model);
                return $"{prop.Name} = '{val}'";
            });
            var sql = new StringBuilder().AppendFormat("UPDATE {0} SET {1} WHERE {2} ",
                TableName, set.JoinToString(), expression.Parse())
                .ToString();
            AccessDb(sqlCommand =>
            {
                sqlCommand.Execute(sql);
            });

        }

        public void Insert<TModel>(TModel Model) where TModel : class, IModel, new()
        {
            var table = new Table(Model);
            var sql = table.GetInsertSql(Model);
            AccessDb(sqlCommand =>
            {
                sqlCommand.Execute(sql);
            });
        }
    }
}
